--/
CREATE PROCEDURE sp_getTopRank 
         @bomTop varchar(50),--母件编号
        @materiel varchar(50),--查询物料编号
        @marks varchar(50)--查询数据显示类型标记
        as
        declare @bomCode varchar(50) --节点编号
        declare @bomTopCode varchar(50)--母件编号
        declare @materielCode varchar(50)--物料编号
        declare @materielName varchar(50)--物料名称
        declare @pCode varchar(50)--父件编号
        declare @counts varchar(50)--用量
        declare @amount varchar(50)--生产单位父级需要的当前物料的数量
        declare @bomTopVersion varchar(50)--母件版本
        declare @orders int--排序号
        declare @pName varchar(50)--父级名
        declare @attritionRate varchar(50)--损耗率
        declare @fixedAttritionRate    varchar(50)--损耗量
        declare @processName    varchar(50)--工艺流程
        declare @materialProcess    varchar(50)  --工序
        declare @rank varchar(10)--层级
        --存储bom树数据临时表
         create table #tempTable (
                       rank varchar(50), 
                       materielCode varchar(50),
                       materielName varchar(50),
                       bomTopVersion varchar(50),
                       pName varchar(50),
                       counts  varchar(50),
                       effectiveDate varchar(50),
                       expiryDate varchar(50),
                       attritionRate varchar(50),
                       fixedAttritionRate varchar(50),
                       processName varchar(50),
                       materialProcess varchar(50),
                       basisNum varchar(10),
                       auditStatusName varchar(50),
                       clienFeed varchar(50),
                       manufacturer varchar(50),
                       auditStatusCode varchar(50),
                       bomCode varchar(50),     
                       pCode varchar(50),
                       amount varchar(50),
                       mark varchar(5),
                        orders  int
                )
                --获取查询物料父级使用量信息临时表
                 create table #topTable (
                       bomTopCode varchar(50), 
                       bomTopName varchar(50),
                       bomTopUnits varchar(50),
                       bomTopCounts varchar(50),
                       pName varchar(50),
                       materielCode varchar(50),
                       materielName varchar(50),
                       materielUnits varchar(50),
                       materielCounts varchar(50),
                       effectiveDate varchar(50),
                       expiryDate varchar(50),
                       attritionRate varchar(50),
                       fixedAttritionRate varchar(50),
                       processName varchar(50),
                       materialProcess varchar(50),
                       basisNum varchar(10),
                       auditStatusName varchar(50),
                       clienFeed varchar(50),
                       manufacturer varchar(50),
                       auditStatusCode varchar(50),
                       bomCode varchar(50),     
                       pCode varchar(50),
                       mark varchar(5)
                )
                --存放返回页面展示的数据的信息临时表
                  create table #topRankTable (
                       bomTopCode varchar(50), 
                       bomTopName varchar(50),
                       bomTopUnits varchar(50),
                       bomTopCounts varchar(50),
                       pName varchar(50),
                        materielCode varchar(50),
                       materielName varchar(50),
                       materielUnits varchar(50),
                       materielCounts varchar(50),
                       effectiveDate varchar(50),
                       expiryDate varchar(50),
                       attritionRate varchar(50),
                       fixedAttritionRate varchar(50),
                       processName varchar(50),
                       materialProcess varchar(50),
                       basisNum varchar(10),
                       auditStatusName varchar(50),
                       clienFeed varchar(50),
                       manufacturer varchar(50),
                       auditStatusCode varchar(50),
                       bomCode varchar(50),     
                       pCode varchar(50),
                       mark varchar(5)
                )
                --累计每一查询物料针对母件的使用总量
                create table #countTable(
                        counts varchar(50),--用量
                        bomCode varchar(50),                  
                        materielCode varchar(50),
                        pName varchar(50)
                )
                --获取bom树递归公共存储过程结果
                insert into #tempTable exec sp_getRecursiveTreeData  @bomTop,@materiel,@marks  
                --创建游标循环物料信息
                declare @totals varchar(50)--存放每一个查询物料对于母件的累计数量
                declare @bomC varchar(50)--存放查询物料父级编号
                declare @materielC varchar(50)--存放查询物料父级物料编号
                declare @materielN varchar(50)--存放查询物料父级物料名称
                declare @num int
                select @num = 0
                declare @ran varchar(50)--存放上一次循环的阶次
                select @totals = '1',@bomC='',@materielC='',@ran = ''
                --声明游标  循环每一个查询物料使用量
              --  select * from #tempTable
                declare c_recursive cursor for select counts,bomCode,pCode,materielCode,orders,materielName,rank ,amount from #tempTable order by orders 
                open c_recursive
                fetch next from c_recursive into @counts,@bomCode,@pCode,@materielCode,@orders,@materielName,@rank,@amount
                while(@@fetch_status = 0)
                begin
                        print 'tatals'
                        print 'bomC-----' + @bomC 
                         print 'materielC-----' + @materielC
                          print 'materielN-----' + @materielN
                           print 'totals-----' + @totals
                        --判断是否循环完一个母件
                        if(@rank = 0)
                        begin                             
                               --select  @totals = cast(@totals as decimal(10,1)) + cast(@counts as decimal(10,1))
                                  --添加数据到临时表                             
                                insert into #countTable select @totals,@bomC,@materielC,@materielN     
                                select    @totals = '1'                
                        end
                        else if(@num = 0 or @ran = 0)
                        begin
                                --统计总使用量
                               -- set  @totals = cast(@totals as decimal(10,1)) + cast(@counts as decimal(10,1))--cast(cast(@counts as decimal(10,1)) * cast(@amount as decimal(10,1)) as decimal(10,1))
                                set @totals = cast(cast(@amount as decimal(10,1)) *cast(@counts as decimal(10,1)) as decimal(10,1))
                        end
                        else
                        begin
                                set @totals = cast(cast(@amount as decimal(10,1)) *cast(@totals as decimal(10,1)) as decimal(10,1))
                        end
                        if(@num = 0)
                        begin
                                select @num = @num + 1
                                select  @bomC = @bomCode,@materielC = @materielCode,@materielN =  @materielName
                        end
                         else if(@ran = 0)
                         begin
                                select  @bomC = @bomCode,@materielC = @materielCode,@materielN =  @materielName
                         end
                         select @ran = @rank
                          fetch next from c_recursive into @counts,@bomCode,@pCode,@materielCode,@orders,@materielName,@rank,@amount
                end
                close c_recursive
                deallocate c_recursive
              --  select * from #countTable
                --查询母件信息
                declare @bomTCode varchar(50)
                declare @bomTopName varchar(50)
                 declare @bomTVersion varchar(50)
                declare @bomUnits varchar(50)--母件单位                               
                select @bomTCode = s.materielCode,@bomTopName = s.materielName, @bomTVersion = s.bomTopVersion, @bomUnits = s1.basicMeasuringUnitName 
                from s_bomTop_info s left join b_materiel_info s1 on s.materielCode = s1.materielCode where s.status = '1' and s1.status = '1'  and s.bomTopCode = @bomTop
                
                --查询待查询物料信息
                declare c_searchM cursor for select  bomCode,materielCode,pCode from s_bom_info where status = '1'  and bomTopCode = @bomTop and materielCode = @materiel
                 open c_searchM
                 
                 fetch next from c_searchM into @bomCode,@materielCode,@pCode
                 while(@@fetch_status = 0)
                 begin
                        select @totals='',@materielC='',@materielN=''
                        select  @totals=counts,@materielC=materielCode,@materielN=pName from #countTable where bomCode = @pCode
                        insert into #topTable select @bomTCode,@bomTopName,@bomUnits,'1',@materielN,s.materielCode,s.materielName,s1.basicMeasuringUnitName,@totals,s.effectiveDate,s.expiryDate,
                        s.attritionRate,s.fixedAttritionRate,case s.processCode when '0' then '' else s.processName end as 'processName',s.materialProcess,s.basisNum,s.auditStatusName,
                         case s.clienFeed when '1' then '是' when '0' then '否' else '' end as 'clienFeed',s.manufacturer, s.auditStatusCode,s.bomCode,s.pCode,'y' as 'mark'  from 
                         s_bom_info s left join b_materiel_info s1 on s.materielCode = s1.materielCode where s.status = '1' and s1.status = '1' and s.bomCode = @bomCode
                         
                         fetch next from c_searchM into @bomCode,@materielCode,@pCode
                 end
                close c_searchM
                deallocate c_searchM
                
                declare @bomTopCodeS varchar(50) --代用物料母件编号
                declare @bomTopNameS varchar(50)--代用物料母件名称
                declare @bomTopUnits varchar(50)--代用物料母件单位
                declare @bomTopCounts varchar(50) --代用物料母件用量
                declare @materielNameS varchar(50)--代用物料名称
                declare @materielUnits varchar(50) --代用物料单位
                declare  @materielCounts varchar(50)--代用物料使用量
                declare @pNameS varchar(50)--代用物料附件名称
                --插入代用物料
                declare c_subs cursor for select  pCode ,materielCode ,bomCode,bomTopCode,bomTopName,bomTopUnits,bomTopCounts,materielCounts,pName from #topTable
                open c_subs
                fetch next from c_subs into @pCode,@materielCode,@bomCode,@bomTopCodeS,@bomTopNameS,@bomTopUnits,@bomTopCounts,@materielCounts,@pNameS
                while(@@fetch_status = 0)
                begin
                        insert into #topRankTable select * from #topTable where bomCode = @bomCode
                        --查询是否有代用
                        declare @result varchar(50)--是否有代用物料 
                        declare @cus varchar(50)--代用物料代用倍数
                        declare @sCode varchar(50)--代用物料编号
                        select @result = '',@cus='1'
                        --声明一个游标  循环代用物料
                        declare c_subsM cursor for select bomPCode,substituteCode,substituteTimes from b_substitute_info where status='1' and bomPCode = @pCode and materielCode = @materielCode
                        open c_subsM
                        fetch next from c_subsM into @result,@sCode,@cus
                        while(@@fetch_status = 0)
                        begin
                                print 'result------' + @result
                                print 'sCode-----' + @sCode
                                 declare @cu varchar(50) --代用物料数量
                                select @cu = cast((cast(@materielCounts as decimal(10,1)) * cast(@cus as decimal(10,1)) ) as decimal(10,1))
                                 print 'cu-----' + @cu
                                --添加代用物料
                                insert into #topRankTable select @bomTopCodeS,@bomTopNameS,@bomTopUnits,@bomTopCounts,@pNameS,s.substituteCode,s.substituteName,s1.basicMeasuringUnitName,
                                @cu,s.effectiveDate,s.expiryDate,'','','','','','','','','','','','n'  from b_substitute_info s left join b_materiel_info s1 on s.substituteCode = s1.materielCode 
                                where s.status = '1' and s1.status = '1' and s.bomPCode = @result and s.substituteCode = @sCode
                                fetch next from c_subsM into @result,@sCode,@cus
                        end
                       close c_subsM
                       deallocate c_subsM
                       fetch next from c_subs into @pCode,@materielCode,@bomCode,@bomTopCodeS,@bomTopNameS,@bomTopUnits,@bomTopCounts,@materielCounts,@pNameS
                end
                close c_subs
                deallocate c_subs
               select * from  #topRankTable
                
                
                
/
